﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using AAA.DataTransfer;
using AAA.Base.Util;

namespace AAA.DataAccess
{
    [Serializable]
    public class DataTableX : DataTable
    {
        private string _strTotal = "Total";
        private const string KEY_DELIMETER = "~";

        // Dictionary<IndexName, Dictionary<FilterValue, List<RowIndex>>>
        private Dictionary<string, Dictionary<string, List<int>>> _dicIndex;

        // Dictonary<IndexName, Dictionary<FilterValue, CalculateResult>>
        private Dictionary<string, Dictionary<string, DataRow>> _dicCalculateResult;

        private Dictionary<string, DataRow> _dicTotal;

        private Dictionary<object, DataTable> _dicPivotResultSet;

        public DataTableX()
        {
            _dicIndex = new Dictionary<string, Dictionary<string, List<int>>>();
            _dicCalculateResult = new Dictionary<string, Dictionary<string, DataRow>>();
            _dicPivotResultSet = new Dictionary<object, DataTable>();
            _dicTotal = new Dictionary<string, DataRow>();
        }

        private string GenerateIndexKey(string[] strKeys, DataRow dataRow, Dictionary<string, ITranslate> dicTranslate)
        {
            if (dicTranslate == null)
                dicTranslate = new Dictionary<string, ITranslate>();

            string[] strValues = new string[strKeys.Length];

            for (int i = 0; i < strKeys.Length; i++)
            {
                strValues[i] = dicTranslate.ContainsKey(strKeys[i])
                                ? dicTranslate[strKeys[i]].Translate(dataRow[strKeys[i]]).ToString()
                                : dataRow[strKeys[i]].ToString();
            }
            return GenerateKey(strValues);
        }

        private string GenerateKey(string[] strKeys)
        {
            string strKey = "";

            for (int i = 0; i < strKeys.Length; i++)
                strKey += KEY_DELIMETER + strKeys[i];

            return strKey;
        }

        private string[] SplitKey(string strKey)
        {

            return strKey.Trim().Length == 0
                    ? new string[0]
                    : strKey.Substring(KEY_DELIMETER.Length).Split(KEY_DELIMETER.ToCharArray());
        }

        private void InitTable(DataTable dataTableSource)
        {
            string[] strColumnNames = new string[Columns.Count];
            for (int i = 0; i < Columns.Count; i++)
                strColumnNames[i] = Columns[i].ColumnName;
            InitTable(dataTableSource, strColumnNames);
        }

        private void InitTable(DataTable dataTableSource, string[] strColumnNames)
        {
            for (int i = 0; i < strColumnNames.Length; i++)
                dataTableSource.Columns.Add(strColumnNames[i], Columns[Columns.IndexOf(strColumnNames[i])].DataType);
        }

        public List<string> GetFilterValues(string[] strKeys)
        {
            string strIndexKey = GenerateKey(strKeys);
            if (!_dicIndex.ContainsKey(strIndexKey))
                return new List<string>();

            return new List<string>(_dicIndex[strIndexKey].Keys);
        }

        public void GenerateIndex(string[] strKeys)
        {
            GenerateIndex(strKeys, new Dictionary<string, ITranslate>());
        }

        public void GenerateIndex(string[] strKeys, Dictionary<string, IGroupAction> dicGroupAction)
        {
            GenerateIndex(strKeys, new Dictionary<string, ITranslate>(), dicGroupAction);
        }

        public void GenerateIndex(string[] strKeys, Dictionary<string, ITranslate> dicKeyTranslate)
        {
            GenerateIndex(strKeys, dicKeyTranslate, new Dictionary<string, IGroupAction>());
        }

        public void GenerateIndex(string[] strKeys, Dictionary<string, ITranslate> dicKeyTranslate, Dictionary<string, IGroupAction> dicGroupAction)
        {
            Dictionary<string, List<int>> dicIndex = new Dictionary<string, List<int>>();
            Dictionary<string, DataRow> dicRowSummary = new Dictionary<string, DataRow>();
            string strIndexKey;
            DataRow dataRow;

            for (int i = 0; i < Rows.Count; i++)
            {
                strIndexKey = GenerateIndexKey(strKeys, Rows[i], dicKeyTranslate);
                if (!dicIndex.ContainsKey(strIndexKey))
                {
                    dicIndex.Add(strIndexKey, new List<int>());
                    dicRowSummary.Add(strIndexKey, NewRow());
                }

                for (int j = 0; j < Columns.Count; j++)
                    if (dicGroupAction.ContainsKey(Columns[j].ColumnName))
                        dicRowSummary[strIndexKey][Columns[j].ColumnName] = dicGroupAction[Columns[j].ColumnName].Group(dicRowSummary[strIndexKey][Columns[j].ColumnName],
                                                                                                                        Rows[i][Columns[j].ColumnName]);

                dicIndex[strIndexKey].Add(i);
            }

            _dicIndex.Add(GenerateKey(strKeys), dicIndex);
            _dicCalculateResult.Add(GenerateKey(strKeys), dicRowSummary);
        }

        private DataRow GenerateRow(DataRow dataRow, int iRowIndex)
        {
            return GenerateRow(dataRow, Rows[iRowIndex]);
        }

        private DataRow GenerateRow(DataRow dataRow, DataRow dataSource)
        {
            for (int i = 0; i < dataRow.Table.Columns.Count; i++)
                dataRow[i] = dataSource[dataRow.Table.Columns[i].ColumnName];

            return dataRow;
        }

        public bool Pivot(object oName, string[] strGroupColumns, string[] strFieldColumns, string[] strValueColumns, List<IGroupAction> lstGroupAction)
        {
            return Pivot(oName, strGroupColumns, strFieldColumns, strValueColumns, ArrayHelper.Create<SortTypeEnum>(SortTypeEnum.Asc, strFieldColumns.Length), lstGroupAction);
        }

        public bool Pivot(object oName, string[] strGroupColumns, string[] strFieldColumns, string[] strValueColumns, SortTypeEnum[] sortBy, List<IGroupAction> lstGroupAction)
        {
            bool isSuccess = false;
            string strSortExpression = "";

            try
            {
                DataRow newDataRow;
                DataTable tblResult = new DataTable();
                DataView dv = DefaultView;
                DataTable processTable;
                string strGroupKey;
                string strFieldKey;
                string[] strGroupValues;
                object[] oValues;

                List<string> lstFieldList = new List<string>();
                List<string> lstGroupList = new List<string>();
                List<DataRow> lstRows;

                //Dictionary<GroupName, Dictionary<FieldName, List<RowData>>>
                Dictionary<string, Dictionary<string, List<DataRow>>> dicCellValue = new Dictionary<string, Dictionary<string, List<DataRow>>>();

                for (int i = 0; i < strFieldColumns.Length; i++)
                    strSortExpression += "," + strFieldColumns[i] + (sortBy[i] == SortTypeEnum.Asc ? " " : " DESC");
                if (strSortExpression.Length > 0)
                    dv.Sort = strSortExpression.Substring(1);

                processTable = dv.Table;

                for (int i = 0; i < processTable.Rows.Count; i++)
                {
                    strFieldKey = GenerateIndexKey(strFieldColumns, processTable.Rows[i], null);
                    strGroupKey = GenerateIndexKey(strGroupColumns, processTable.Rows[i], null);

                    if (lstGroupList.IndexOf(strGroupKey) < 0)
                    {
                        lstGroupList.Add(strGroupKey);
                        dicCellValue.Add(strGroupKey, new Dictionary<string, List<DataRow>>());
                    }
                    if (lstFieldList.IndexOf(strFieldKey) < 0)
                    {
                        lstFieldList.Add(strFieldKey);
                        dicCellValue[strGroupKey].Add(strFieldKey, new List<DataRow>());
                    }

                    dicCellValue[strGroupKey][strFieldKey].Add(Rows[i]);
                }


                for (int i = 0; i < strGroupColumns.Length; i++)
                    tblResult.Columns.Add(strGroupColumns[i]);

                for (int i = 0; i < lstFieldList.Count; i++)
                {
                    for (int j = 0; j < strValueColumns.Length; j++)
                        tblResult.Columns.Add(lstFieldList[i] + KEY_DELIMETER + strValueColumns[j]);
                }

                for (int i = 0; i < lstGroupList.Count; i++)
                {
                    newDataRow = tblResult.NewRow();
                    strGroupValues = SplitKey(lstGroupList[i]);

                    for (int j = 0; j < strGroupColumns.Length; j++)
                        newDataRow[strGroupColumns[j]] = strGroupValues[j];

                    for (int j = 0; j < lstFieldList.Count; j++)
                    {
                        if (dicCellValue[lstGroupList[i]].ContainsKey(lstFieldList[j]) == false)
                            continue;

                        lstRows = dicCellValue[lstGroupList[i]][lstFieldList[j]];

                        for (int k = 0; k < strValueColumns.Length; k++)
                        {
                            //Init Row Data
                            newDataRow[lstFieldList[i] + KEY_DELIMETER + strValueColumns[k]] = lstRows[0][strValueColumns[k]];

                            //Group Row Data
                            for (int l = 1; l < lstRows.Count; l++)
                            {
                                newDataRow[lstFieldList[i] + KEY_DELIMETER + strValueColumns[k]] = lstGroupAction[k].Group(newDataRow[lstFieldList[i] + KEY_DELIMETER + strValueColumns[k]],
                                                                                         lstRows[l][strValueColumns[k]]);
                            }
                        }
                    }

                    tblResult.Rows.Add(newDataRow);
                }

                _dicPivotResultSet.Add(oName, tblResult);

                isSuccess = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isSuccess;
        }

        public void Join(object oName, DataTable dataTable, string[] strKeys)
        {
            Join(oName, dataTable, strKeys, strKeys);
        }

        public void Join(object oName, DataTable dataTable, string[] strSourceKeys, string[] strTargetKeys)
        {
            List<string> lstColumn = new List<string>();

            for (int i = 0; i < dataTable.Columns.Count; i++)
                lstColumn.Add(dataTable.Columns[i].ColumnName);
            for (int i = 0; i < strTargetKeys.Length; i++)
                lstColumn.Remove(strTargetKeys[i]);

            Join(oName, dataTable, strSourceKeys, strTargetKeys, lstColumn.ToArray());
        }

        public void Join(object oName, DataTable dataTable, string[] strSourceKeys, string[] strTargetKeys, string[] strColumnNames)
        {
            Join(oName, dataTable, strSourceKeys, strTargetKeys, strColumnNames, strColumnNames, new Dictionary<string, IGroupAction>());
        }

        public void Join(object oName, DataTable dataTable, string[] strSourceKeys, string[] strTargetKeys, string[] strSourceColumnNames, string[] strTargetColumnNames,
                         Dictionary<string, IGroupAction> dicGroupAction)
        {
            Join(oName, dataTable, strSourceKeys, strTargetKeys, strSourceColumnNames, strTargetColumnNames, dicGroupAction, JoinTypeEnum.Left);
        }

        public void Join(object oName, DataTable dataTable, string[] strSourceKeys, string[] strTargetKeys, string[] strSourceColumnNames, string[] strTargetColumnNames,
                         Dictionary<string, IGroupAction> dicGroupAction, JoinTypeEnum eJoinType)
        {
            DataTable sourceTable = oName == null
                                               ? this
                                               : _dicPivotResultSet.ContainsKey(oName)
                                                    ? _dicPivotResultSet[oName]
                                                    : this;

            Dictionary<string, List<int>> dicRowIndex = new Dictionary<string, List<int>>();
            string strKeyIndex;
            List<int> lstRowIndex;

            for (int i = 0; i < strTargetColumnNames.Length; i++)
                if (sourceTable.Columns.IndexOf(strTargetColumnNames[i]) < 0)
                    sourceTable.Columns.Add(strTargetColumnNames[i], typeof(string));


            for (int i = 0; i < sourceTable.Rows.Count; i++)
            {
                strKeyIndex = GenerateIndexKey(strSourceKeys, sourceTable.Rows[i], new Dictionary<string, ITranslate>());
                if (!dicRowIndex.ContainsKey(strKeyIndex))
                    dicRowIndex.Add(strKeyIndex, new List<int>());
                dicRowIndex[strKeyIndex].Add(i);
            }


            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                strKeyIndex = GenerateIndexKey(strTargetKeys, dataTable.Rows[i], new Dictionary<string, ITranslate>());
                if (!dicRowIndex.ContainsKey(strKeyIndex))
                    continue;

                lstRowIndex = dicRowIndex[strKeyIndex];

                for (int j = 0; j < lstRowIndex.Count; j++)
                {
                    for (int k = 0; k < strTargetColumnNames.Length; k++)
                    {
                        if (dicGroupAction.ContainsKey(strTargetColumnNames[k]))
                        {
                            sourceTable.Rows[lstRowIndex[j]][strTargetColumnNames[k]] = dicGroupAction[strTargetColumnNames[k]].Group(sourceTable.Rows[lstRowIndex[j]][strTargetColumnNames[k]],
                                                                                                                          dataTable.Rows[i][strSourceColumnNames[k]]);
                        }
                        else
                        {
                            sourceTable.Rows[lstRowIndex[j]][strTargetColumnNames[k]] = dataTable.Rows[i][strSourceColumnNames[k]];
                        }
                    }
                }
            }
        }

        public void RowRatio(object oName, string strNumerator, string strDenominator, string strRatioName)
        {
            DataTable sourceTable = oName == null
                                               ? this
                                               : _dicPivotResultSet.ContainsKey(oName)
                                                    ? _dicPivotResultSet[oName]
                                                    : this;
            double dRatio;
            string strNumeratorValue;
            string strDenominatorValue;

            if ((sourceTable.Columns.IndexOf(strNumerator) < 0) ||
               (sourceTable.Columns.IndexOf(strDenominator) < 0))
                return;

            if (sourceTable.Columns.IndexOf(strRatioName) < 0)
                sourceTable.Columns.Add(strRatioName, typeof(string));

            for (int i = 0; i < sourceTable.Rows.Count; i++)
            {
                strNumeratorValue = sourceTable.Rows[i][strNumerator].ToString().Trim();
                strDenominatorValue = sourceTable.Rows[i][strDenominator].ToString().Trim();

                if (strNumeratorValue == "")
                    strNumeratorValue = "0";

                if ((strDenominatorValue == "") ||
                    (strDenominatorValue == "0"))
                    dRatio = double.NaN;
                //dRatio = 0;
                else
                    dRatio = double.Parse(strNumeratorValue)
                           / double.Parse(strDenominatorValue);

                if (double.IsNaN(dRatio) || double.IsInfinity(dRatio))
                {
                    sourceTable.Rows[i][strRatioName] = "0.00%";
                }
                else
                {
                    sourceTable.Rows[i][strRatioName] = (dRatio * 100).ToString("0.00") + "%";
                }
            }
        }

        public void CalculateRatio(object oName, string[] strColumnNames, string[] strCountColumns, string[] strRatioColumnNames)
        {
            CalculateRatio(oName, strColumnNames, strCountColumns, null, strRatioColumnNames);
        }

        public void CalculateRatio(object oName, string[] strColumnNames, string[] strCountColumns, string strTotalColumnName, string[] strRatioColumnNames)
        {
            DataTable sourceTable = oName == null
                                               ? this
                                               : _dicPivotResultSet.ContainsKey(oName)
                                                    ? _dicPivotResultSet[oName]
                                                    : this;

            if (strCountColumns.Length != strRatioColumnNames.Length)
                return;

            string strTotalKey;
            DataRow currentRow;
            double dRatio;
            string strNumeratorValue;
            string strDenominatorValue;

            for (int i = 0; i < strRatioColumnNames.Length; i++)
                if (sourceTable.Columns.IndexOf(strRatioColumnNames[i]) < 0)
                    sourceTable.Columns.Add(strRatioColumnNames[i], typeof(string));

            for (int i = 0; i < sourceTable.Rows.Count; i++)
            {
                strTotalKey = GenerateIndexKey(strColumnNames, sourceTable.Rows[i], new Dictionary<string, ITranslate>());
                if (!_dicTotal.ContainsKey(strTotalKey))
                    continue;
                currentRow = _dicTotal[strTotalKey];

                for (int j = 0; j < strCountColumns.Length; j++)
                {
                    if ((currentRow[strTotalColumnName == null
                                    ? strCountColumns[j]
                                    : strTotalColumnName].ToString().Trim() == "") ||
                        (currentRow[strTotalColumnName == null
                                    ? strCountColumns[j]
                                    : strTotalColumnName].ToString().Trim() == "0"))
                    {
                        sourceTable.Rows[i][strRatioColumnNames[j]] = "0.00%";
                        continue;
                    }

                    strNumeratorValue = sourceTable.Rows[i][strCountColumns[j]].ToString();
                    strDenominatorValue = currentRow[strTotalColumnName == null
                                                        ? strCountColumns[j]
                                                        : strTotalColumnName].ToString().Trim();
                    if (strNumeratorValue == "")
                        strNumeratorValue = "0";

                    if (double.Parse(strDenominatorValue) == 0)
                    {
                        dRatio = float.NaN;
                    }
                    else
                    {
                        dRatio = double.Parse(strNumeratorValue)
                                / double.Parse(strDenominatorValue);
                    }
                    if (double.IsInfinity(dRatio) ||
                       double.IsNaN(dRatio))
                    {
                        sourceTable.Rows[i][strRatioColumnNames[j]] = "0.00%";
                    }
                    else
                    {
                        sourceTable.Rows[i][strRatioColumnNames[j]] = (dRatio * 100).ToString("0.00") + "%";
                    }
                }
            }
        }

        public void AttachTotal(object oName, string strTotalName, string[] strColumnNames, string[] strAttachNames)
        {
            DataTable sourceTable = oName == null
                                               ? this
                                               : _dicPivotResultSet.ContainsKey(oName)
                                                    ? _dicPivotResultSet[oName]
                                                    : this;
            DataRow currentRow;
            List<string[]> lstGroupField = new List<string[]>();
            string strFieldName;
            string strIndexKey;

            for (int i = 0; i < strAttachNames.Length; i++)
            {
                if (sourceTable.Columns.IndexOf(strAttachNames[i]) < 0)
                    sourceTable.Columns.Add(strAttachNames[i], typeof(string));
            }

            strFieldName = "";
            for (int i = 0; i < strColumnNames.Length; i++)
            {
                strFieldName += "," + strColumnNames[i];
                lstGroupField.Add(strFieldName.Substring(1).Split(','));
            }

            for (int i = 0; i < sourceTable.Rows.Count; i++)
            {
                currentRow = sourceTable.Rows[i];

                for (int j = 0; j < lstGroupField.Count; j++)
                {
                    strIndexKey = GenerateIndexKey(lstGroupField[j], currentRow, new Dictionary<string, ITranslate>());
                    if (!_dicTotal.ContainsKey(strIndexKey))
                        continue;
                    currentRow[strAttachNames[j]] = _dicTotal[strIndexKey][strTotalName];
                }

            }
        }

        public void CalculateRowTotal(object oName, string[] strValueFieldNames, string strTotalFieldName)
        {
            CalculateRowTotal(oName, strValueFieldNames, strTotalFieldName, typeof(string));
        }
        public void CalculateRowTotal(object oName, string[] strValueFieldNames, string strTotalFieldName, Type tColumnType)
        {
            DataTable sourceTable = oName == null
                                               ? this
                                               : _dicPivotResultSet.ContainsKey(oName)
                                                    ? _dicPivotResultSet[oName]
                                                    : this;
            IGroupAction groupAction = new NumericGroupAction();

            sourceTable.Columns.Add(strTotalFieldName, tColumnType);

            for (int i = 0; i < sourceTable.Rows.Count; i++)
            {
                sourceTable.Rows[i][strTotalFieldName] = 0;
                for (int j = 0; j < strValueFieldNames.Length; j++)
                {
                    sourceTable.Rows[i][strTotalFieldName] = groupAction.Group(sourceTable.Rows[i][strTotalFieldName], sourceTable.Rows[i][strValueFieldNames[j]]);
                }
            }

        }

        public void CalculateColTotal(object oName, string[] strValueFieldNames)
        {
            CalculateColTotal(oName, strValueFieldNames, 0);
        }

        public void CalculateColTotal(object oName, string[] strValueFieldNames, int iTotalTitleColIndex)
        {
            DataTable sourceTable = oName == null
                                               ? this
                                               : _dicPivotResultSet.ContainsKey(oName)
                                                    ? _dicPivotResultSet[oName]
                                                    : this;
            IGroupAction groupAction = new NumericGroupAction();

            DataRow dataRow = sourceTable.NewRow();
            object oDataType;

            for (int i = 0; i < sourceTable.Columns.Count; i++)
            {
                oDataType = sourceTable.Columns[i].DataType;
                switch (sourceTable.Columns[i].DataType.Name)
                {
                    case "String":
                        dataRow[i] = "";
                        break;
                    default:
                        dataRow[i] = 0;
                        break;
                }
            }

            dataRow[iTotalTitleColIndex] = "總計";
            for (int i = 0; i < strValueFieldNames.Length; i++)
                dataRow[strValueFieldNames[i]] = 0;

            for (int i = 0; i < sourceTable.Rows.Count; i++)
            {
                for (int j = 0; j < strValueFieldNames.Length; j++)
                {
                    dataRow[strValueFieldNames[j]] = groupAction.Group(dataRow[strValueFieldNames[j]], sourceTable.Rows[i][strValueFieldNames[j]]);
                }
            }

            sourceTable.Rows.Add(dataRow);
        }

        public void CalculateTotal(object oName, string[] strColumnNames)
        {
            CalculateTotal(oName, strColumnNames, null);
        }

        public void CalculateTotal(object oName, string[] strColumnNames, string[] strValueColumns)
        {
            DataTable sourceTable = oName == null
                                               ? this
                                               : _dicPivotResultSet.ContainsKey(oName)
                                                    ? _dicPivotResultSet[oName]
                                                    : this;
            string[] strCurrentColumnNames;
            List<string> lstValueColumn;

            if (strValueColumns == null)
            {
                lstValueColumn = new List<string>();
                //產生Value的ColumnName
                for (int i = 0; i < sourceTable.Columns.Count; i++)
                    lstValueColumn.Add(sourceTable.Columns[i].ColumnName);
                for (int i = 0; i < strColumnNames.Length; i++)
                    lstValueColumn.Remove(strColumnNames[i]);
            }
            else
            {
                lstValueColumn = new List<string>(strValueColumns);
            }

            for (int i = 0; i < strColumnNames.Length; i++)
            {
                strCurrentColumnNames = new string[i + 1];
                for (int j = 0; j < strCurrentColumnNames.Length; j++)
                    strCurrentColumnNames[j] = strColumnNames[j];
                CalculateTotal(sourceTable, lstValueColumn, strCurrentColumnNames);
            }

        }


        private void CalculateTotal(DataTable sourceTable, List<string> lstValueColumn, string[] strColumnNames)
        {

            double dValue;
            DataRow dataRow;
            //保存目前比對的Key值
            string strGroupKey = "";
            //保存目前Row產生的Key值
            string strCurrentGroupKey = "";
            //保存要插入subtotal的位置
            int iInsertPosition = sourceTable.Rows.Count;
            //保存目前的subtotal值
            double[] dTotals = ArrayHelper.Create<double>(0, lstValueColumn.Count);

            string[] strValues;

            for (int i = 0; i < sourceTable.Columns.Count; i++)
            {
                if (sourceTable.Columns[i].DataType == typeof(string))
                    sourceTable.Columns[i].MaxLength = 1000;
                sourceTable.Columns[i].ReadOnly = false;
            }

            for (int i = sourceTable.Rows.Count - 1; i >= 0; i--)
            {
                strCurrentGroupKey = GenerateIndexKey(strColumnNames, sourceTable.Rows[i], new Dictionary<string, ITranslate>());
                if ((strGroupKey != strCurrentGroupKey) && (strGroupKey != ""))
                {
                    dataRow = sourceTable.NewRow();

                    strValues = SplitKey(strGroupKey);
                    for (int j = 0; j < strColumnNames.Length; j++)
                        dataRow[strColumnNames[j]] = strValues[j];

                    for (int j = 0; j < lstValueColumn.Count; j++)
                        dataRow[lstValueColumn[j]] = dTotals[j];

                    if (_dicTotal.ContainsKey(strGroupKey))
                    {
                        _dicTotal[strGroupKey] = dataRow;
                    }
                    else
                    {
                        _dicTotal.Add(strGroupKey, dataRow);
                    }
                    dTotals = new double[lstValueColumn.Count];
                }

                for (int j = 0; j < dTotals.Length; j++)
                {
                    dValue = NumericHelper.ParseDouble(sourceTable.Rows[i][lstValueColumn[j]] != null
                                                                ? sourceTable.Rows[i][lstValueColumn[j]].ToString()
                                                                : "0");
                    if (!double.IsNaN(dValue))
                        dTotals[j] += dValue;
                    else
                        dTotals[j] += 0;
                }
                strGroupKey = strCurrentGroupKey;
            }


            dataRow = sourceTable.NewRow();

            strValues = SplitKey(strGroupKey);
            for (int j = 0; j < strColumnNames.Length; j++)
            {
                dataRow[strColumnNames[j]] = (j < strValues.Length ? strValues[j] : "");
            }

            for (int j = 0; j < lstValueColumn.Count; j++)
                dataRow[lstValueColumn[j]] = dTotals[j];

            if (_dicTotal.ContainsKey(strGroupKey))
            {
                _dicTotal[strGroupKey] = dataRow;
            }
            else
            {
                _dicTotal.Add(strGroupKey, dataRow);
            }
        }

        public void CalculateDistinctTotal(object oName, List<string> lstValueColumn, string[] strColumnNames, int iTotalRowIndex)
        {

            DataTable sourceTable = oName == null
                                               ? this
                                               : _dicPivotResultSet.ContainsKey(oName)
                                                    ? _dicPivotResultSet[oName]
                                                    : this;
            double dValue;
            DataRow dataRow;
            //保存目前比對的Key值
            string strGroupKey = "";
            //保存目前Row產生的Key值
            string strCurrentGroupKey = "";

            //保存目前的subtotal值
            double[] dTotals = ArrayHelper.Create<double>(0, lstValueColumn.Count);
            Dictionary<string, double[]> dicDistinctValue = new Dictionary<string, double[]>();
            string[] strValues;

            for (int i = 0; i < sourceTable.Columns.Count; i++)
            {
                if (sourceTable.Columns[i].DataType == typeof(string))
                    sourceTable.Columns[i].MaxLength = 1000;
                sourceTable.Columns[i].ReadOnly = false;
            }


            for (int i = sourceTable.Rows.Count - 1; i >= 0; i--)
            {
                if (i == iTotalRowIndex)
                    continue;

                strCurrentGroupKey = GenerateIndexKey(strColumnNames, sourceTable.Rows[i], new Dictionary<string, ITranslate>());

                if (dicDistinctValue.ContainsKey(strCurrentGroupKey) == false)
                    dicDistinctValue.Add(strCurrentGroupKey, new double[lstValueColumn.Count]);

                for (int j = 0; j < lstValueColumn.Count; j++)
                {
                    dValue = NumericHelper.ParseDouble(sourceTable.Rows[i][lstValueColumn[j]] != null
                                                                ? sourceTable.Rows[i][lstValueColumn[j]].ToString()
                                                                : "0");
                    if (!double.IsNaN(dValue))
                        dicDistinctValue[strCurrentGroupKey][j] = dValue;
                    else
                        dicDistinctValue[strCurrentGroupKey][j] = 0;
                }
            }

            dataRow = sourceTable.Rows[iTotalRowIndex];

            foreach (double[] dValues in dicDistinctValue.Values)
                for (int j = 0; j < dValues.Length; j++)
                    dTotals[j] += dValues[j];

            for (int j = 0; j < lstValueColumn.Count; j++)
                dataRow[lstValueColumn[j]] = dTotals[j];
        }

        /*
                public DataTable CalculateTotal(object oName, string[] strColumnNames)
                {
                    DataTable sourceTable = oName == null
                                               ? this
                                               : _dicPivotResultSet.ContainsKey(oName)
                                                    ? _dicPivotResultSet[oName]
                                                    : this;

                    DataRow dataRow;
                    //保存目前比對的Key值
                    string[] strGroupKeys = ArrayHelper.Create<string>("", strColumnNames.Length);
                    //保存目前Row產生的Key值
                    string[] strCurrentGroupKeys = ArrayHelper.Create<string>("", strColumnNames.Length);
                    //保存要插入subtotal的位置
                    int[] iInsertPosition = ArrayHelper.Create<int>(sourceTable.Rows.Count - 1, strColumnNames.Length);
                    //保存目前的subtotal值
                    List<double[]> lstSubtobal = new List<double[]>();
                    List<string> lstValueColumn = new List<string>();
                    int iOffset;

                    //產生值的ColumnName
                    for (int i = 0; i < sourceTable.Columns.Count; i++)
                        lstValueColumn.Add(sourceTable.Columns[i].ColumnName);
                    for (int i = 0; i < strColumnNames.Length; i++)
                        lstValueColumn.Remove(strColumnNames[i]);

                    for(int i = 0; i < strColumnNames.Length; i++)
                        lstSubtobal.Add(ArrayHelper.Create<double>(0, sourceTable.Columns.Count - strColumnNames.Length));

                    for (int i = 0; i < iInsertPosition.Length; i++)
                        iInsertPosition[i] = sourceTable.Rows.Count;

                    for (int i = 0; i < sourceTable.Columns.Count; i++)
                    {
                        if(sourceTable.Columns[i].DataType == typeof(string))
                            sourceTable.Columns[i].MaxLength = 1000;
                        sourceTable.Columns[i].ReadOnly = false;
                    }
                    for (int i = sourceTable.Rows.Count - 1; i >= 0; i--)
                    {
                        for (int j = 0; j < strCurrentGroupKeys.Length; j++)
                            strCurrentGroupKeys[j] = "";

                        //for (int j = strColumnNames.Length - 1; j >= 0; j--)
                        for (int j = 0; j < strColumnNames.Length; j++)
                        {
                            for(int k = j; k >= 0; k--)
                                strCurrentGroupKeys[j] += "," + sourceTable.Rows[i][strColumnNames[k]].ToString();
                            iOffset = 0;
                            if (strCurrentGroupKeys[j] != strGroupKeys[j] && (strGroupKeys[j] != ""))
                            {

                                dataRow = sourceTable.NewRow();
                                iOffset++;
                                for (int k = j; k < strColumnNames.Length; k++)
                                    //dataRow[strColumnNames[k]] = strGroupKeys[j] + " " + _strTotal;
                                    dataRow[strColumnNames[k]] = strGroupKeys[j];

                                for (int k = 0; k < lstValueColumn.Count; k++)
                                    dataRow[lstValueColumn[k]] = lstSubtobal[j][k];

                                //將subtotal加至table上
                                sourceTable.Rows.InsertAt(dataRow, iInsertPosition[j]);

                                //將上層的Subtotal RowIndex 加1
                                for (int k = j - 1; k >= 0; k--)
                                {
                                    //dataRow[strColumnNames[k]] = sourceTable.Rows[i][strColumnNames[k]];       
                                    iInsertPosition[k]++;
                                }
                                //將各層的Subtotal 歸零
                                for (int k = j; k < lstSubtobal.Count; k++)
                                {
                                    iInsertPosition[k] = i + iOffset;
                                    lstSubtobal[k] = ArrayHelper.Create<double>(0, sourceTable.Columns.Count - strColumnNames.Length);
                                }                        
                            }                    
                        }

                        for (int j = 0; j < strCurrentGroupKeys.Length; j++)
                            strGroupKeys[j] = strCurrentGroupKeys[j];
                        //將值累計至subtotal的內容
                        for(int j = 0; j < lstSubtobal.Count; j++)
                            for (int k = 0; k < lstValueColumn.Count; k++)
                                lstSubtobal[j][k] += double.Parse(sourceTable.Rows[i][lstValueColumn[k]].ToString());
                    }
                        
                    for (int j = strColumnNames.Length - 1; j >= 0; j--)
                    {
                        dataRow = sourceTable.NewRow();
                        for (int k = j; k >= 0; k--)
                            dataRow[strColumnNames[k]] = strGroupKeys[j] + " " + _strTotal;

                        for (int k = 0; k < lstValueColumn.Count; k++)
                            dataRow[lstValueColumn[k]] = lstSubtobal[j][k];

                        //將subtotal加至table上
                        sourceTable.Rows.InsertAt(dataRow, iInsertPosition[j]);
                    }
 
                    return sourceTable;
                }
        */
        public DataTable Distinct()
        {
            string[] strKeys = new string[Columns.Count];

            for (int i = 0; i < Columns.Count; i++)
                strKeys[i] = Columns[i].ColumnName;

            return Distinct(strKeys);
        }

        public DataTableX Union(DataTable dataTable, string[] strFirstKeys, string[] strSecondKeys)
        {
            DataTableX resultTable = new DataTableX();
            DataRow dataRow;
            Dictionary<string, List<int>> dicRowIndex = new Dictionary<string, List<int>>();
            string strKey;
            List<int> lstRowIndex;

            for (int i = 0; i < Columns.Count; i++)
                resultTable.Columns.Add(Columns[i].ColumnName, Columns[i].DataType);

            for (int i = 0; i < dataTable.Columns.Count; i++)
                if (resultTable.Columns.IndexOf(dataTable.Columns[i].ColumnName) < 0)
                    resultTable.Columns.Add(dataTable.Columns[i].ColumnName, dataTable.Columns[i].DataType);

            List<string> lstColumn = new List<string>();
            for (int i = 0; i < resultTable.Columns.Count; i++)
                lstColumn.Add(resultTable.Columns[i].ColumnName);

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                dataRow = resultTable.NewRow();

                for (int j = 0; j < dataTable.Columns.Count; j++)
                    dataRow[dataTable.Columns[j].ColumnName] = dataTable.Rows[i][j];

                strKey = GenerateIndexKey(strFirstKeys, dataRow, new Dictionary<string, ITranslate>());
                if (!dicRowIndex.ContainsKey(strKey))
                    dicRowIndex.Add(strKey, new List<int>());
                dicRowIndex[strKey].Add(resultTable.Rows.Count);

                resultTable.Rows.Add(dataRow);
            }

            for (int i = 0; i < Rows.Count; i++)
            {
                strKey = GenerateIndexKey(strFirstKeys, Rows[i], new Dictionary<string, ITranslate>());
                if (!dicRowIndex.ContainsKey(strKey))
                {
                    dataRow = resultTable.NewRow();
                    for (int j = 0; j < Columns.Count; j++)
                        dataRow[Columns[j].ColumnName] = Rows[i][j];
                    resultTable.Rows.Add(dataRow);
                    continue;
                }

                lstRowIndex = dicRowIndex[strKey];
                for (int j = 0; j < lstRowIndex.Count; j++)
                {
                    for (int k = 0; k < Columns.Count; k++)
                        resultTable.Rows[lstRowIndex[j]][Columns[k].ColumnName] = Rows[i][k];
                }
            }

            return resultTable;
        }

        public DataTable Distinct(string[] strKeys)
        {
            List<string> lstContent = new List<string>();
            string strValue;
            DataTable dataTable = new DataTable();

            try
            {
                InitTable(dataTable, strKeys);
                for (int i = 0; i < Rows.Count; i++)
                {
                    strValue = GenerateIndexKey(strKeys, Rows[i], null);
                    if (lstContent.IndexOf(strValue) < 0)
                    {
                        dataTable.Rows.Add(GenerateRow(dataTable.NewRow(), Rows[i]));
                        lstContent.Add(strValue);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public DataTable Filter(string[] strKeys, string[] strValues)
        {
            return Filter(strKeys, strValues, new Dictionary<string, ITranslate>());
        }

        public DataTable Filter(string[] strKeys, string[] strValues, AppendPositionEnum appendPosition)
        {
            return Filter(strKeys, strValues, new Dictionary<string, ITranslate>(), appendPosition);
        }

        public DataTable Filter(string[] strKeys, string[] strValues, Dictionary<string, ITranslate> dicKeyTranslate)
        {
            return Filter(strKeys, strValues, dicKeyTranslate, AppendPositionEnum.None);
        }

        public DataTableX Sort(string strSortExpress)
        {
            DataTableX sortedTable = null;

            try
            {
                DefaultView.Sort = strSortExpress;


                // 但是此排序並非dt實際排序...須轉換
                sortedTable = CreateTable(DefaultView);


            }
            catch (Exception ex)
            {
                throw;
            }
            return sortedTable;
        }

        // 把DataView轉換為DataTabl

        public DataTableX CreateTable(DataView obDataView)
        {
            if (null == obDataView)
            {
                throw new ArgumentNullException
                ("DataView", "Invalid DataView object specified");
            }

            DataTableX obNewDt = new DataTableX();

            for (int i = 0; i < Columns.Count; i++)
                obNewDt.Columns.Add(Columns[i].ColumnName, Columns[i].DataType);

            int idx = 0;
            string[] strColNames = new string[obNewDt.Columns.Count];

            foreach (DataColumn col in obNewDt.Columns)
            {
                strColNames[idx++] = col.ColumnName;
            }

            IEnumerator<DataRowView> viewEnumerator = (IEnumerator<DataRowView>)obDataView.GetEnumerator();
            while (viewEnumerator.MoveNext())
            {
                DataRowView drv = (DataRowView)viewEnumerator.Current;
                DataRow dr = obNewDt.NewRow();
                try
                {
                    foreach (string strName in strColNames)
                    {
                        dr[strName] = drv[strName];
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                obNewDt.Rows.Add(dr);
            }

            return obNewDt;

        }


        public DataTable Filter(string[] strKeys, string[] strValues, Dictionary<string, ITranslate> dicKeyTranslate, AppendPositionEnum appendPosition)
        {
            string strIndexKey = GenerateKey(strKeys);
            string strFilterKey;
            DataTable returnTable = new DataTable();
            InitTable(returnTable);
            DataRow dataRow = null;

            if (!_dicIndex.ContainsKey(strIndexKey))
            {
                GenerateIndex(strKeys, dicKeyTranslate);
            }

            strFilterKey = GenerateKey(strValues);

            if (!_dicIndex[strIndexKey].ContainsKey(strFilterKey))
                return returnTable;


            foreach (int iRowIndex in _dicIndex[strIndexKey][strFilterKey])
            {
                returnTable.Rows.Add(GenerateRow(returnTable.NewRow(), iRowIndex));
            }

            if (_dicCalculateResult.ContainsKey(strIndexKey))
            {
                if (_dicCalculateResult[strIndexKey].ContainsKey(strFilterKey))
                    dataRow = GenerateRow(returnTable.NewRow(), _dicCalculateResult[strIndexKey][strFilterKey]);

                switch (appendPosition)
                {
                    case AppendPositionEnum.First:
                        returnTable.Rows.InsertAt(dataRow, 0);
                        break;
                    case AppendPositionEnum.Last:
                        returnTable.Rows.Add(dataRow);
                        break;
                }
            }

            return returnTable;
        }
    }
}
